﻿using Infrastructure.Models;
using Infrastructure.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using Dapper;
using System.Runtime.Caching;

namespace Infrastructure.DAL
{
    /// <summary>
    /// 设备变量值
    /// </summary>
    public class DeviceVarDAL
    {
        public static MemoryCache cache = new MemoryCache("Device");
        public static int SaveDevice(Device device)
        {
            using (IDbConnection conn =
                new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                conn.Open();
                var tran = conn.BeginTransaction();
                int rows = 0;
                int id = device.id;
                if (device.id == 0)
                {
                   
                    //插入设备表的记录
                    id = conn.Query<int>(@"insert into device(device_name,protocol,device_ip,device_port,create_time,create_user,update_time,update_user)
values(@device_name,@protocol,@device_ip,@device_port,@create_time,@create_user,@update_time,@update_user);
select last_insert_rowid() from device; ", device, tran).FirstOrDefault();
                    if (id > 0)
                        rows = 1;
                }
                else
                {
                    
                    //修改
                    rows = conn.Execute(@"update device set 
device_name=@device_name,
protocol=@protocol,
device_ip=@device_ip,
device_port=@device_port,
update_time=@update_time,
update_user=@update_user where id=@id ", device, tran);
                }
                conn.Execute("delete from device_var where device_id=@device_id",new { device_id=id});
                //插入设备关联的变量记录
                foreach (var item in device.VarList)
                {
                   item.device_id = id;
                   rows+= conn.Execute(@"insert into device_var(device_id,alert_var,var_name,var_type,var_length,var_address,create_time,create_user,update_time,update_user) 
values(@device_id,@alert_var,@var_name,@var_type,@var_length,@var_address,@create_time,@create_user,@update_time,@update_user);", item,tran);
                }
                tran.Commit();
                return rows;
                
            }
        }

        public static List<DeviceVar> GetDeviceVarListByDeviceId(int id)
        {
            using (IDbConnection conn =
               new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                return conn.Query<DeviceVar>(@"select * from device_var where device_id=@device_id;",
                    new { device_id=id}).ToList();
            }
        }

        public static List<DeviceVarVal> GetDeviceVarValBy(int deviceId,
            int varId,DateTime begin,DateTime end)
        {
            using (IDbConnection conn =
              new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                string sql = @"select t0.device_name,t1.var_name,t1.var_address,m.* from device_var_val m left join device t0 on 
m.device_id=t0.id left join device_var t1 on m.var_id=t1.id
where m.device_id=@deviceId and m.var_id=@varId and m.create_time >=@begin and m.create_time <=@end ";
                
                return conn.Query<DeviceVarVal>(sql,
                    new { deviceId = deviceId,varId=varId,
                        begin=begin.ToString("yyyy-MM-dd HH:mm:ss"),
                        end=end.ToString("yyyy-MM-dd HH:mm:ss")
                    }).ToList();
            }
        }

        public static List<DeviceVar> GetVarListIncludeDeviceInfoBy(
            int deviceId, string varName)
        {
            using(IDbConnection conn=new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                string sql = @"select t0.device_name,m.* from device_var m 
 left join device t0 on m.device_id=t0.id 
 where  m.device_id=@deviceId ";
                if (!string.IsNullOrEmpty(varName))
                {
                    sql += " and m.var_name = @varName ";
                }

               return  conn.Query<DeviceVar>(sql
,new {  varName=varName,deviceId= deviceId }).ToList();
            }
        }

        public static List<Device> GetDeviceList()
        {
            using (IDbConnection conn =
                new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                 return conn.Query<Device>(@"select * from device;").ToList();
            }
        }

        public static int RemoveDeviceById(int id)
        {
            using (IDbConnection conn =
    new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                conn.Open();
                var tran = conn.BeginTransaction();
                int rows=  conn.Execute("delete from device_var where device_id=@device_id", new { device_id = id }, tran);
                rows+=conn.Execute("delete from device where id=@id", new { id = id }, tran);
                tran.Commit();
                return rows;

            }
        }

        public static int SaveVarData(List<DeviceVar> varList)
        {
            List<DeviceVarVal> list = new List<DeviceVarVal>();
            foreach (var item in varList)
            {
                DeviceVarVal val = new DeviceVarVal();
                val.device_id = item.device_id;
                val.var_id = item.id;
                val.var_val = item.var_val;
                val.create_time = item.create_time;
                list.Add(val);
            }
            using(IDbConnection conn=new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                conn.Open();
                var tran = conn.BeginTransaction();
                int rows = 0;
                foreach (var item in list)
                {
                   rows+= conn.Execute("insert into device_var_val(device_id,var_id,var_val,create_time) " +
                        "values(@device_id,@var_id,@var_val,@create_time) ", item,tran);
                }
                tran.Commit();
                return rows;
            }
           
        }

        public static List<Device> GetDeviceListFromCache()
        {
           var obj= cache.Get("alldevice");
            if (obj == null)
            {
                var list = GetDeviceList();
                cache.Set("alldevice", list, DateTimeOffset.Now.AddHours(8));
                return list;
            }
            else
            {
                return obj as List<Device>;
            }
        }

        public static List<DeviceVar> GetDeviceVarListByDeviceIdFromCache(
            int id)
        {
            var obj = cache.Get("device_"+id);
            if (obj == null)
            {
                var list = GetDeviceVarListByDeviceId(id);
                cache.Set("device_"+id, list, DateTimeOffset.Now.AddHours(8));
                return list;
            }
            else
            {
                return obj as List<DeviceVar>;
            }
        }

        public static Device GetDeviceBy(int device_id)
        {
            using (IDbConnection conn =
              new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                return conn.Query<Device>(@"select * from device where id=@id;",
                    new { id=device_id}).FirstOrDefault();
            }
        }
    }
}
